﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Xml;
using System.Data.SqlClient;
using System.IO;

namespace Pets
{
    public class PetFinder
    {
        private string path;
        private Dictionary<string, List<string>> petkindsAndOwners;
        public PetFinder(string path)
        {
            this.path = path;
            petkindsAndOwners = new Dictionary<string, List<string>>();
        }

        public string Path
        {
            get
            {
                return path;
            }
            set
            {
                path = value;
            }
        }

        public Dictionary<string, List<string>> FindAllMatches() 
        {

            XDocument doc = new XDocument();
            try
            {
                doc = XDocument.Load(path);
            }
            catch (IOException ex)
            {
                Console.WriteLine(ex.Message);
            }
            var petKinds = doc.Descendants("pet");
            foreach (var petKind in petKinds) 
            {
                string owner = petKind.FirstAttribute.Value;
                string pet = petKind.LastAttribute.Value;

                if (petkindsAndOwners.ContainsKey(pet))
                {
                    petkindsAndOwners[pet].Add(owner);
                }
                else 
                {
                    petkindsAndOwners.Add(pet, new List<string>());
                    petkindsAndOwners[pet].Add(owner);
                }
            }

            return petkindsAndOwners;
        }

        public IOrderedEnumerable<KeyValuePair<string,List<string>>> WriteMatches() 
        {
            string fileName;
            Encoding encoding;
            SetFileNameAndEncoding(out fileName, out encoding);

            IOrderedEnumerable<KeyValuePair<string,List<string>>> sortPairs =  petkindsAndOwners.OrderBy(pet => pet.Key);

            using (XmlTextWriter writer = new XmlTextWriter(fileName, encoding)) 
            {
                FormatDocument(writer);
                writer.WriteStartDocument();
                writer.WriteStartElement("petkinds");
                foreach (var item in sortPairs) 
                {
                    item.Value.Sort();
                    writer.WriteStartElement("petkind");
                    writer.WriteAttributeString("type",item.Key);
                    foreach (var owner in item.Value) 
                    {
                        writer.WriteStartElement("owner");
                        writer.WriteAttributeString("name",owner);
                        writer.WriteEndElement();
                    }
                    writer.WriteEndElement();
                }
                writer.WriteEndDocument();
            }
            return sortPairs;
        }

        private void SetFileNameAndEncoding(out string fileName, out Encoding encoding)
        {
            fileName = "../../owners.xml";
            encoding = Encoding.GetEncoding("utf-8");
        }

        private void FormatDocument(XmlTextWriter writer)
        {
            writer.Formatting = Formatting.Indented;
            writer.IndentChar = '\t';
            writer.Indentation = 1;
        }

        public void AddMatchesToDataBase() 
        {
            SqlConnection dbConnection = new SqlConnection("Server=.\\SQLEXPRESS; " +
               "Database= Petkinds; Integrated Security=true");
            dbConnection.Open();
            using (dbConnection) 
            {
                foreach (var item in petkindsAndOwners.Keys) 
                {
                    string query = "Insert Into PetKinds (PetKind) " +
                        "VALUES (@PetKind)";
                    SqlCommand insetComand = new SqlCommand(query, dbConnection);
                    insetComand.Parameters.Add(new SqlParameter("@PetKind",item));
                    insetComand.ExecuteNonQuery();
                    query = "select Max(PetKindId) from Petkinds";
                    SqlCommand selectId = new SqlCommand (query,dbConnection);
                    int petId = (int)selectId.ExecuteScalar();
                    foreach (var owner in petkindsAndOwners[item]) 
                    {
                        query = "Insert Into Owners (PetKindId,Ownename) " +
                        "VALUES (@PetKindId,@Ownername)";
                        SqlCommand insertOwner = new SqlCommand(query,dbConnection);
                        insertOwner.Parameters.Add(new SqlParameter("@PetKindId",petId));
                        insertOwner.Parameters.Add(new SqlParameter("@Ownername", owner));
                        insertOwner.ExecuteNonQuery();
                    }
                }
            }
        }
    }
}
